package com.dao.impl;

import com.dao.ShopDao;
import com.entity.Foods;
import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ShopDaoImpl implements ShopDao {

    private QueryRunner runner=new QueryRunner();

    @Override
    public List<Foods> findShop(int typeId) {
        Connection connection = DruidUtils.getConnection();
        List<Foods> foods=null;
        String sql = "select * from foods where type_id=?;";
        try {
            foods = runner.query(connection,sql,new BeanListHandler<>(Foods.class),typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }


    /**
     * 查询所有食物存进map集合
     * @return
     */
    @Override
    public List<Map<String, Object>> foodFindAll() {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID GROUP BY food_id ";
        try {
            foods = runner.query(connection,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    @Override
    public List<Map<String, Object>> shopFind(int pageNum, int startCount) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID GROUP BY food_id LIMIT ?,?";
        pageNum=(pageNum-1)*8;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),pageNum,startCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 按照最低价排序
     */
    @Override
    public List<Map<String, Object>> listPrice(int pageNum,int startCount) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID  GROUP BY food_id ORDER BY f.price LIMIT ?,?";
        pageNum=(pageNum-1)*8;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),pageNum,startCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 按照最高价排序
     */
    @Override
    public List<Map<String, Object>> listPriceDesc(int pageNum,int startCount) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID  GROUP BY food_id ORDER BY f.price desc LIMIT ?,?";
        pageNum=(pageNum-1)*8;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),pageNum,startCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 按照最多赞排序
     */
    @Override
    public List<Map<String, Object>> lisLikeDesc(int pageNum,int startCount) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID  GROUP BY food_id ORDER BY  `like`  DESC LIMIT ?,?";
        pageNum=(pageNum-1)*8;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),pageNum,startCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 查询数据条数
     * @return
     */
    @Override
    public int getPriceCount(int max ,int min){
        Connection connection = DruidUtils.getConnection();
        String sql = "select count(id) from foods WHERE  price<? and price>?";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>(),max,min);
            int count = date.intValue();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }


    /**
     * 匹配价格
     */
    @Override
    public List<Map<String, Object>> findPrice(int max,int min,int pageNum,int startCount) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID and f.price<? and f.price>?  GROUP BY food_id ORDER BY f.price LIMIT ?,?";
        pageNum=(pageNum-1)*8;
        try {
            foods = runner.query(connection,sql,new MapListHandler(),max,min,pageNum,startCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     *模糊查询
     */
    @Override
    public List<Map<String, Object>> findName(String name) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID and f.`name` like \"%?%\"  GROUP BY food_id ";
        try {
            foods = runner.query(connection,sql,new MapListHandler(),name);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 查询点赞最多的4条数据
     */
    @Override
    public List<Map<String, Object>> lisLikeFour() {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> foods=null;
        String sql = "SELECT f.ID,f.`name`,f.photo,f.business_id,f.price,f.type_id,f.`describe`,SUM(`like`) `like` FROM `like` l,foods f WHERE l.food_id=f.ID  GROUP BY food_id ORDER BY  `like`  DESC LIMIT 4";
        try {
            foods = runner.query(connection,sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return foods;
    }

    /**
     * 查询数据条数
     * @return
     */
    @Override
    public int getCount(){
        Connection connection = DruidUtils.getConnection();
        String sql = "select count(id) from foods";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>());
            int count = date.intValue();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }


}
